import os
import sys
import pymysql
import xlwt
from PyQt5.QtWidgets import *
from PyQt5.QtCore import Qt, QRegExp
from PyQt5.QtGui import QIcon, QRegExpValidator

class ClassData2Show(QWidget):
    def __init__(self):
        super(ClassData2Show, self).__init__()
        self.setWindowTitle("班级管理数据")
        self.setWindowIcon(QIcon("./gui_image/gui_Icon.png"))
        self.setFixedSize(800, 600)
        self.initUI()

    def initUI(self):
        layout = QVBoxLayout()

        # 搜索和操作区域
        operation_layout = QHBoxLayout()
        
        self.data2Delete = QLineEdit()
        self.data2Delete.setPlaceholderText("请输入班级编号")
        data2Delete_val = QRegExpValidator(QRegExp("^[0-9]{8}$"))
        self.data2Delete.setValidator(data2Delete_val)
        
        self.button2Delete = QPushButton("删除")
        self.Fresh = QPushButton("刷新")
        self.save2excel = QPushButton("导出Excel")
        
        operation_layout.addWidget(QLabel("班级编号:"))
        operation_layout.addWidget(self.data2Delete)
        operation_layout.addWidget(self.button2Delete)
        operation_layout.addWidget(self.Fresh)
        operation_layout.addWidget(self.save2excel)
        operation_layout.addStretch()

        # 表格区域
        self.tableWidget = QTableWidget()
        
        layout.addLayout(operation_layout)
        layout.addWidget(self.tableWidget)
        
        self.setLayout(layout)
        
        self.buttonEnable()
        self.dataFresh()

    def buttonEnable(self):
        self.data2deleteTest()
        self.data2Delete.textChanged.connect(self.data2deleteTest)
        self.Fresh.clicked.connect(self.dataFresh)
        self.button2Delete.clicked.connect(self.dataDelete)
        self.save2excel.clicked.connect(self.save2Excel)

    def data2deleteTest(self):
        if self.data2Delete.text():
            self.button2Delete.setEnabled(True)
        else:
            self.button2Delete.setEnabled(False)

    def dataFresh(self):
        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()
        cursor.execute('select * from classtable')
        rows = cursor.fetchall()
        row = cursor.rowcount
        vol = len(rows[0]) if rows else 0

        cursor.close()
        conn.close()

        self.tableWidget.setRowCount(row)
        self.tableWidget.setColumnCount(vol)

        self.tableWidget.setHorizontalHeaderLabels(['班级编号', '班级名称', '班级人数', 'Id格式','签到开始时间','签到结束时间'])
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.tableWidget.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        for i in range(row):
            for j in range(vol):
                temp_data = rows[i][j]
                data = QTableWidgetItem(str(temp_data))
                self.tableWidget.setItem(i, j, data)

    def save2Excel(self):
        save2path = './save2Excel/classtable.xls'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()
        cursor.execute('select * from classtable')
        results = cursor.fetchall()
        fields = cursor.description

        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet('classtable', cell_overwrite_ok=True)

        for field in range(0, len(fields)):
            sheet.write(0, field, fields[field][0])

        for row in range(1, len(results) + 1):
            for col in range(0, len(fields)):
                sheet.write(row, col, u'%s' % results[row - 1][col])

        workbook.save(save2path)
        QMessageBox.about(self, '提示', "导出成功！")

        cursor.close()
        conn.close()

    def dataDelete(self):
        data2Delete_tempor = self.data2Delete.text()

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )

        cursor = conn.cursor()

        sql = 'select * from classtable where majorNumber = %s'
        rows = cursor.execute(sql, data2Delete_tempor)

        if rows:
            sql_delete = 'delete from classtable where majorNumber = %s'
            cursor.execute(sql_delete, data2Delete_tempor)
            conn.commit()
            QMessageBox.about(self, "提示", "删除成功！")
            self.dataFresh()
        else:
            QMessageBox.warning(self, "警告", "班级不存在，请重新核对班级编号！")

        cursor.close()
        conn.close()


class LessonData2Show(QWidget):
    def __init__(self):
        super(LessonData2Show, self).__init__()
        self.setWindowTitle("课程管理数据")
        self.setWindowIcon(QIcon("./gui_image/gui_Icon.png"))
        self.setFixedSize(800, 600)
        self.initUI()

    def initUI(self):
        layout = QVBoxLayout()

        operation_layout = QHBoxLayout()
        
        self.data2Delete = QLineEdit()
        self.data2Delete.setPlaceholderText("如:14021702信号与系统")
        data2Delete_val = QRegExpValidator(QRegExp("[0-9\u4e00-\u9fa5]{25}$"))
        self.data2Delete.setValidator(data2Delete_val)
        
        self.button2Delete = QPushButton("删除")
        self.Fresh = QPushButton("刷新")
        self.save2excel = QPushButton("导出Excel")
        
        operation_layout.addWidget(QLabel("课程信息:"))
        operation_layout.addWidget(self.data2Delete)
        operation_layout.addWidget(self.button2Delete)
        operation_layout.addWidget(self.Fresh)
        operation_layout.addWidget(self.save2excel)
        operation_layout.addStretch()

        self.tableWidget = QTableWidget()
        
        layout.addLayout(operation_layout)
        layout.addWidget(self.tableWidget)
        
        self.setLayout(layout)
        
        self.buttonEnable()
        self.dataFresh()

    def buttonEnable(self):
        self.data2deleteTest()
        self.data2Delete.textChanged.connect(self.data2deleteTest)
        self.Fresh.clicked.connect(self.dataFresh)
        self.button2Delete.clicked.connect(self.dataDelete)
        self.save2excel.clicked.connect(self.save2Excel)

    def data2deleteTest(self):
        if self.data2Delete.text():
            self.button2Delete.setEnabled(True)
        else:
            self.button2Delete.setEnabled(False)

    def dataFresh(self):
        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()
        cursor.execute('select * from lessontable')
        rows = cursor.fetchall()
        row = cursor.rowcount
        vol = len(rows[0]) if rows else 0

        cursor.close()
        conn.close()

        self.tableWidget.setRowCount(row)
        self.tableWidget.setColumnCount(vol)

        self.tableWidget.setHorizontalHeaderLabels(['班级编号', '课程名称', '开课日期', '结课日期','一周课表','上课时间','下课时间'])
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.tableWidget.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        for i in range(row):
            for j in range(vol):
                temp_data = rows[i][j]
                data = QTableWidgetItem(str(temp_data))
                self.tableWidget.setItem(i, j, data)

    def save2Excel(self):
        save2path = './save2Excel/lessontable.xls'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()
        cursor.execute('select * from lessontable')
        results = cursor.fetchall()
        fields = cursor.description

        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet('lessontable', cell_overwrite_ok=True)

        for field in range(0, len(fields)):
            sheet.write(0, field, fields[field][0])

        for row in range(1, len(results) + 1):
            for col in range(0, len(fields)):
                sheet.write(row, col, u'%s' % results[row - 1][col])

        workbook.save(save2path)
        QMessageBox.about(self, '提示', "导出成功！")

        cursor.close()
        conn.close()

    def dataDelete(self):
        data2Delete_tempor = self.data2Delete.text()
        data2Delete_classNum = data2Delete_tempor[0:8]
        data2Delete_className = data2Delete_tempor[8:]

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )

        cursor = conn.cursor()

        sql = 'select * from lessontable where toClass = %s and lessonName = %s'
        rows = cursor.execute(sql, (data2Delete_classNum, data2Delete_className))

        if rows:
            sql_delete = 'delete from lessontable where toClass = %s and lessonName = %s'
            cursor.execute(sql_delete, (data2Delete_classNum, data2Delete_className))
            conn.commit()
            QMessageBox.about(self, "提示", "删除成功！")
            self.dataFresh()
        else:
            QMessageBox.warning(self, "警告", "输入错误，请重新核对输入信息！")

        cursor.close()
        conn.close()